package cn.smvc.wechat.test.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.stereotype.Service;

import cn.smvc.common.dao.JDBCUtils;
import cn.smvc.wechat.test.dao.KinderLocationDao;
import cn.smvc.wechat.test.entity.KinderLocation;

@Service
public class KinderLocationDaoImpl implements KinderLocationDao{
	
	/*
	 * 这两个是处理数据库表字段下划线的，
	 * 		与实体类中的属性对应（驼峰规则）
	 */
	BeanProcessor bean = new GenerousBeanProcessor();
	RowProcessor processor = new BasicRowProcessor(bean);

	@Override
	public int save(KinderLocation location) {
		// 添加用户的sql
		String sql = "insert into kinder_location values(null,?,?,?,?,?,?,?,now(),now(),0) ";
		// 用JDBCUtils工具创建一个queryRunner对象
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		int result = 0;
		try {
			// 用queryRunner保存用户信息
			result = queryRunner.update(sql, 
					location.getKinderName(),
					location.getKinderAddr(),
					location.getLocationX(),
					location.getLocationY(),
					location.getScale(),
					location.getCreateBy(),
					location.getUpdateBy());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int update(KinderLocation location) {
		// 添加用户的sql
		String sql = "update kinder_location set kinder_name=?, "
				+ "kinder_addr=?, "
				+ "location_x=?, "
				+ "location_y=?, "
				+ "scale=?, "
				+ "update_by=?, "
				+ "update_date=?, "
				+ "del_flag=? "
				+ "where id=?";
		// 用JDBCUtils工具创建一个queryRunner对象
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		int result = 0;
		try {
			// 用queryRunner保存用户信息
			result = queryRunner.update(sql, 
					location.getKinderName(),
					location.getKinderAddr(),
					location.getLocationX(),
					location.getLocationY(),
					location.getScale(),
					location.getUpdateBy(),
					location.getUpdateDate(),
					location.getDelFlag(), 
					location.getId());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public KinderLocation getByCreateBy(int createBy) {
		String sql = "select * from kinder_location where create_by=? and del_flag=0 order by create_date desc";
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		KinderLocation location = null;
		try {
			List<KinderLocation> kinderLocationList = queryRunner.query(sql, 
					new BeanListHandler<KinderLocation>(KinderLocation.class, processor), 
					createBy);
			if(kinderLocationList != null && kinderLocationList.size() > 0) {
				location = kinderLocationList.get(0);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return location;
	}

	@Override
	public KinderLocation getByKinderName(String kinderName) {
		String sql = "select * from kinder_location where kinder_name=? and del_flag=0 order by create_date desc";
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		KinderLocation location = null;
		try {
			List<KinderLocation> kinderLocationList = queryRunner.query(sql, 
					new BeanListHandler<KinderLocation>(KinderLocation.class, processor), 
					kinderName);
			if(kinderLocationList != null && kinderLocationList.size() > 0) {
				location = kinderLocationList.get(0);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return location;
	}

	@Override
	public int updateOverTimeDel() {
		
		String sql = "update kinder_location k set k.del_flag=1 where k.del_flag=0 and k.location_x is null and k.create_date<=date_add(now(), interval - 5 minute) ";
		// 用JDBCUtils工具创建一个queryRunner对象
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		int result = 0;
		try {
			// 用queryRunner保存用户信息
			result = queryRunner.update(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public KinderLocation getLastOne(String toUserName, String fromUserName) {
		StringBuilder sql = new StringBuilder();
		sql
		.append("select k.* from kinder_location k      ")
		.append("join wechat_user u on u.id=k.update_by ")
		.append("where k.del_flag=0 and u.delFlag=0     ")
		.append("  and u.toUserName=?                   ")
		.append("  and u.fromUserName=?                 ")
		.append("order by k.update_date desc            ")
		;
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		KinderLocation location = null;
		try {
			List<KinderLocation> kinderLocationList = queryRunner.query(sql.toString(), 
					new BeanListHandler<KinderLocation>(KinderLocation.class, processor), 
					toUserName,
					fromUserName);
			if(kinderLocationList != null && kinderLocationList.size() > 0) {
				location = kinderLocationList.get(0);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return location;
	}

}
